﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Assessment.Management.SharedComponent.Params.ManagerParam;
using Assessment.Management.SharedComponent.Constants;
using Assessment.Management.SharedComponent.Entities;
using System.Data.SqlClient;

namespace Assessment.Management.DAO.CommonManagers
{
    public class TeacherDAO :BaseDaos
    {
        public TeacherDAO()
            : base()
        {
        }

        public void GetListTeacherByDepartmentID(TeacherParam param)
        {

            string query = @"Select * from Teachers where Deleted=@Deleted and DepartmentID=@DepartmentID";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            cmd.Parameters.AddWithValue("@DepartmentID", param.Teacher.DepartmentID);
            try
            {
                param.listTeacher = base.ExecuteQueryGetListObject<Teacher>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void GetListTeacher(TeacherParam param)
        {
            string query = "Select * from Teachers where Deleted=@Deleted";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            try
            {
                param.listTeacher = base.ExecuteQueryGetListObject<Teacher>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void GetTeacherToEditByCode(TeacherParam param)
        {
            string query = "Select * from Teachers where Deleted=@Deleted and ID=@ID";
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            cmd.Parameters.AddWithValue("@ID", param.Teacher.ID);

            try
            {
                param.Teacher = base.ExecuteQueryGetObject<Teacher>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void CreateTeacher(TeacherParam param)
        {
            string query = @"Insert into Teachers (ID, TeacherName, DepartmentID, Address, Mobile, Email, CreatedBy, CreatedDTG, Deleted, Version) 
                                        values(@ID, @TeacherName, @DepartmentID, @Address, @Mobile, @Email, @CreatedBy, @CreatedDTG, @Deleted, @Version)";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@ID", param.Teacher.ID);
            cmd.Parameters.AddWithValue("@TeacherName", param.Teacher.TeacherName);
            cmd.Parameters.AddWithValue("@DepartmentID", param.Teacher.DepartmentID);
            cmd.Parameters.AddWithValue("@Address", param.Teacher.Address);
            cmd.Parameters.AddWithValue("@Mobile", param.Teacher.Mobile);
            cmd.Parameters.AddWithValue("@Email", param.Teacher.Email);
            cmd.Parameters.AddWithValue("@CreatedBy", param.Teacher.CreatedBy);
            cmd.Parameters.AddWithValue("@CreatedDTG", param.Teacher.CreatedDTG);
            cmd.Parameters.AddWithValue("@Deleted", param.Teacher.Deleted);
            cmd.Parameters.AddWithValue("@Version", param.Teacher.Version);

            try
            {
                int count = base.ExecuteNonQuery(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void UpdateTeacher(TeacherParam param, SqlCommand sqlCmd = null)
        {
            string query = @"Update Teachers 
                              Set TeacherName  = @TeacherName, 
                                DepartmentID   = @DepartmentID, 
                                Address        = @Address,
                                Mobile         = @Mobile,
                                Email          = @Email,
                                Version        = Version+1,
                                UpdatedBy      = @UpdatedBy,
                                UpdatedDTG     = @UpdatedDTG
                            where ID         = @ID and Version=@Version";

            // if SqlCommand argument is null, it means this function is not in transaction
            bool isUsingTransaction = sqlCmd != null;
            if (sqlCmd == null)
            {
                sqlCmd = base.GetSqlCommand();
            }

            sqlCmd.CommandText = query;
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@ID", param.Teacher.ID);
            sqlCmd.Parameters.AddWithValue("@TeacherName", param.Teacher.TeacherName);
            sqlCmd.Parameters.AddWithValue("@DepartmentID", param.Teacher.DepartmentID);
            sqlCmd.Parameters.AddWithValue("@Address", param.Teacher.Address);
            sqlCmd.Parameters.AddWithValue("@Mobile", param.Teacher.Mobile);
            sqlCmd.Parameters.AddWithValue("@Email", param.Teacher.Email);
            sqlCmd.Parameters.AddWithValue("@UpdatedBy", param.Teacher.UpdatedBy);
            sqlCmd.Parameters.AddWithValue("@UpdatedDTG", param.Teacher.UpdatedDTG);
            sqlCmd.Parameters.AddWithValue("@Version", param.Teacher.Version);


            try
            {
                int count = base.ExecuteNonQuery(sqlCmd);
            }
            finally
            {
                // If this function is using transaction from biz, do not close connection
                if (!isUsingTransaction)
                {
                    base.CloseConnection(sqlCmd);
                }
            }
        }

        public void DeleteTeachers(TeacherParam param, SqlCommand sqlCmd = null)
        {
            var query = @"Update Teachers
                          Set Deleted        = @Deleted,
                              Version        = Version+1,
                              UpdatedBy      = @UpdatedBy,
                              UpdatedDTG     = @UpdatedDTG
                          Where ID = @ID and Version=@Version";

            // if SqlCommand argument is null, it means this function is not in transaction
            bool isUsingTransaction = sqlCmd != null;
            if (sqlCmd == null)
            {
                sqlCmd = base.GetSqlCommand();
            }
            List<Teacher> lstDeleteTeacher = param.listTeacher;
            sqlCmd.CommandText = query;

            try
            {
                foreach (Teacher item in lstDeleteTeacher)
                {
                    sqlCmd.Parameters.Clear();
                    sqlCmd.Parameters.AddWithValue("@Deleted", item.Deleted);
                    sqlCmd.Parameters.AddWithValue("@Version", item.Version);
                    sqlCmd.Parameters.AddWithValue("@UpdatedBy", item.UpdatedBy);
                    sqlCmd.Parameters.AddWithValue("@UpdatedDTG", item.UpdatedDTG);
                    sqlCmd.Parameters.AddWithValue("@ID", item.ID);
                    int count = base.ExecuteNonQuery(sqlCmd);
                }
            }
            finally
            {
                // If this function is using transaction from biz, do not close connection
                if (!isUsingTransaction)
                {
                    base.CloseConnection(sqlCmd);
                }
            }
        }

        public void SearchTeachers(TeacherParam param)
        {
            string query = @"select ID, TeacherName, DepartmentID,Address,Mobile,Email from Teachers 
                                where (@TeacherName='' or TeacherName like N'%'+@TeacherName+N'%') 
                                                                             and Deleted=@Deleted";
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@ID", param.Teacher.ID);
            cmd.Parameters.AddWithValue("@TeacherName", param.Teacher.TeacherName);
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            try
            {
                param.listTeacher = base.ExecuteQueryGetListObject<Teacher>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void CheckExitsTeacherData(TeacherParam param)
        {
            var dah = new DataAccessHelper();
            string query = @"select count(*) from Teachers where Deleted=@Deleted and ID = @ID";
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            cmd.Parameters.AddWithValue("@ID", param.Teacher.ID);
            try
            {
                param.Teacher.ID = base.ExecuteScalar(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void GetLastTeacherCode(TeacherParam param)
        {
            //string query = "select top 1 Ma from test2";
            string query = "select top 1 ID from Teachers where Deleted=@Deleted order by CreatedDTG desc";
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            try
            {
                param.Teacher = base.ExecuteQueryGetObject<Teacher>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }
       
    }
}

